<?php 
    /**
     * 业务逻辑类型
     * 
     * @author jinyue.wang
     * @version 1.0
     * @date 2013-12-12
     */
     class ZEdu{
         
         public $conn;
         public function __construct() {
             $this->conn = Yii::app()->db;
         }
         
         /**
          * 得到科目基本信息
          * 
          * @return array();
          * 
          * @version 1.0
          * @author jinyue.wang
          * @date 2013-12-13
          */
         public function _getSubjectObject(){
             
             $data = array();
             
             $sql = 'SELECT "subject_id","subject_name","subject_code" FROM "{{subject}}" WHERE "is_del" = 0 AND "disabled"=1 ';
             $result = $this->conn->createCommand($sql);
             $row = $result->queryAll();
             if(!empty($row)){
                 foreach($row as $value){
                     $data[$value['subject_id']] = $value;
                 }
             }
             
             return $data;
         }
         
         /**
          * 角色基本信息
          * 
          * @return array()
          * 
          * @version 1.0
          * @author jinyue.wang
          * @date 2013-12-29
          */
         public function _getRoleObject(){
             
             $data = array();
             
             $sql = 'SELECT "role_id","role_name","role_code" FROM "{{role}}" ';
             $result = $this->conn->createCommand($sql);
             $row = $result->queryAll();
             if(!empty($row)){
                 foreach($row as $value){
                    $data[$value['role_id']] = $value; 
                 }
             }
             
             return $data;
         }
         
         /**
          * 指定角色信息
          * 
          * @param $role_id 
          * @return array
          * 
          * @author jinyue.wang
          * @version 1.0
          * @date 2013-12-12
          */
         public function _getRoleArray($role_id){
             
             $data = array();
             
             $sql = 'SELECT "role_id","role_name","role_code","description" FROM "{{role}}" WHERE "role_id" = :role_id';
             $result = $this->conn->createCommand($sql);
             $result->bindParam(':role_id',$role_id,PDO::PARAM_STR);
             $data = $result->queryRow();
             
             return $data;
         }
         
         /**
          * 得到功能列表信息
          * 
          * @param $where
          * 
          * @return array()
          * 
          * @author jinyue.wang
          * @version 1.0
          * @date 2013-12-12
          */
         public function _getMenuData($where = ''){
             
             $data = array();
             
             $sql = 'SELECT "id","ename","cname","parentid","m","c","a","display","comment","disabled" FROM "{{menu}}" '.$where.' ORDER BY "listorder" ASC ';

             $result = $this->conn->createCommand($sql);
             $data = $result->queryAll();
             
             return $data;
         }
         
         /**
          * 得到当前角色功能
          * 
          * @param role_id
          * 
          * @return array
          * 
          * @version 1.0
          * @date 2013-12-12
          * @version 1.0
          */
         public function _getRoleMenu($role_id){
             if(empty($role_id))
                 return null;
             
             $sql = 'SELECT "menu_id","m","c","a" FROM "{{role_priv}}" WHERE "role_id" = :role_id ';
             $result = $this->conn->createCommand($sql);
             $result->bindParam(':role_id', $role_id,PDO::PARAM_STR);
             $data = $result->queryAll();
             
             return $data;
         }

         /**
          * 更新（添加角色功能）
          * 
          * @param $mid
          * @param $data
          * 
          * @return boolean
          * 
          * @version 1.0
          * @author jinyue.wang
          * @date 2013-12-14
          */
         public function _updRoleMenu($mid,$data){
             
             $status = 0;
             
             if($mid>0){
                 
                $sql = 'SELECT "id" FROM "{{menu}}" WHERE "m"=:m AND "c"=:c AND "a"=:a AND "id" != '.$mid;
                $result = $this->conn->createCommand($sql);
                $result->bindParam(':m', $data['m'], PDO::PARAM_STR);
                $result->bindParam(':c', $data['c'], PDO::PARAM_STR);
                $result->bindParam(':a', $data['a'], PDO::PARAM_STR);
                $mData = $result->queryRow();

                if (empty($mData)) {
                 $sql = 'UPDATE "{{menu}}" SET "ename"=:ename_s,"cname"=:cname_s,"parentid"=:pid_s,"m"=:m_s,"c"=:c_s,"a"=:a_s,"comment"=:comment_s,"display"=:display_s WHERE "id"=:id_s ';
                 $result = $this->conn->createCommand($sql);
                 
                 $display = 0;
                 if(isset($data['check'])){
                     $display = 1;
                 }
                 
                 $result->bindParam(':ename_s', $data['ename'],PDO::PARAM_STR);
                 $result->bindParam(':cname_s', $data['cname'],PDO::PARAM_STR);
                 $result->bindParam(':pid_s',$data['parentid'],PDO::PARAM_STR);
                 $result->bindParam(':m_s',$data['m'],PDO::PARAM_STR);
                 $result->bindParam(':c_s',$data['c'],PDO::PARAM_STR);
                 $result->bindParam(':a_s',$data['a'],PDO::PARAM_STR);
                 $result->bindParam(':comment_s',$data['comment'],PDO::PARAM_STR);
                 $result->bindParam(':display_s',$display,PDO::PARAM_STR);
                 $result->bindParam(':id_s',$data['mid'],PDO::PARAM_STR);
                 
                 $status = $result->execute();
                }
                 
             }else{
                 
                 $display = 0;
                 if(isset($data['check'])){
                     $display = 1;
                 }
                 
                $sql = 'SELECT "id" FROM "{{menu}}" WHERE "m"=:m AND "c"=:c AND "a"=:a ';
                $result = $this->conn->createCommand($sql);
                $result->bindParam(':m', $data['m'], PDO::PARAM_STR);
                $result->bindParam(':c', $data['c'], PDO::PARAM_STR);
                $result->bindParam(':a', $data['a'], PDO::PARAM_STR);
                $mData = $result->queryRow();

                if (empty($mData)) {
                 $sql = 'INSERT INTO "{{menu}}" ("ename","cname","parentid","m","c","a","comment","display") VALUES(:ename_s,:cname_s,:pid_s,:m_s,:c_s,:a_s,:comment_s,:display_s)';
                 $result = $this->conn->createCommand($sql);
                 
                 $result->bindParam(':ename_s', $data['ename'],PDO::PARAM_STR);
                 $result->bindParam(':cname_s', $data['cname'],PDO::PARAM_STR);
                 $result->bindParam(':pid_s',$data['parentid'],PDO::PARAM_STR);
                 $result->bindParam(':m_s',$data['m'],PDO::PARAM_STR);
                 $result->bindParam(':c_s',$data['c'],PDO::PARAM_STR);
                 $result->bindParam(':a_s',$data['a'],PDO::PARAM_STR);
                 $result->bindParam(':display_s',$display,PDO::PARAM_STR);
                 $result->bindParam(':comment_s',$data['comment'],PDO::PARAM_STR);
                 
                 $status = $result->execute();
                }
             }
             
             return $status;
         }
         
         /**
          * 得到学校信息
          * 
          * @return array()
          * 
          * @version 1.0
          * @author jinyue.wang
          * @date 2013-12-15
          */
         public function _getSchoolInfo($school_ids=''){
             
             $sql = 'SELECT "school_id","school_name","school_nick","school_code","edu_type_ids" FROM "{{school}}" ';
             if(!empty($school_ids)){
             	$sql .= " WHERE \"school_id\" IN (".$school_ids.")";
             }
             $result = $this->conn->createCommand($sql);
             
             $data = $result->queryAll();
             
             return $data;       
         }
         
         /**
          * 更新用户信息
          * 
          * @param $role_id
          * @param $data
          * 
          * @return array
          * 
          * @author jinyue.wang
          * @date 2013-12-16
          * @version 1.0
          */
         public function _updUserRoe($role_id,$data){
             
             $sql = 'UPDATE "{{role}}" SET "role_name" = :role_name,"role_code" = :role_code,"description" = :description WHERE "role_id" = :role_id ';
             $result = $this->conn->createCommand($sql);
             
             $result->bindParam(':role_name', $data['role_name'],PDO::PARAM_STR);
             $result->bindParam(':role_code', $data['role_code'],PDO::PARAM_STR);
             $result->bindParam(':description',$data['description'],PDO::PARAM_STR);
             $result->bindParam(':role_id',$role_id,PDO::PARAM_STR);
             
             $status = $result->execute();
            $menuArray = isset($data['menu_id']) ? $data['menu_id'] : array();
                 $this->_delRoleMenu($role_id);
            if (!empty($menuArray)) {
                foreach ($menuArray as $val) {
                    $data_role = $this->_isRoleMenu($role_id,$val);
                    if(empty($data_role)){
                        $this->_addRoleMenu($role_id, $val,true);
                    }
                 }
             }
             return $status;
         }
         
         /**
          * 判断当前角色是否存在功能
          * 
          * @param $role_id
          * @param $menu_id
          * 
          * @return boolean
          * 
          * @version 1.0
          * @date 2013-12-28
          */
         public function _isRoleMenu($role_id,$menu_id){
             if(empty($role_id) || empty($menu_id))
                 return null;
             
             $sql = 'SELECT "role_id","menu_id" FROM "{{role_priv}}" WHERE "role_id" = :role_id AND "menu_id" = :menu_id';
             $result = $this->conn->createCommand($sql);
             $result->bindParam(':role_id',$role_id,PDO::PARAM_STR);
             $result->bindParam(':menu_id',$menu_id,PDO::PARAM_STR);
             $row = $result->queryRow();
             
             return $row;
         }
         
         /**
          * 得到角色的基础功能
          * 
          * @param $role_id
          * 
          * @return array()
          * 
          * @version 1.0
          * @author jinyue.wang
          * @date 2013-12-29
          */
         public function _getRolePrive($role_id){
             if(empty($role_id))
                 return null;
             
             $data = array();
             
             $sql = 'SELECT "role_id","menu_id" FROM "{{role_priv}}" WHERE "role_id" = :role_id ';
             $result = $this->conn->createCommand($sql);
             $result->bindParam(':role_id',$role_id,PDO::PARAM_STR);
             $row = $result->queryAll();
             if(!empty($row)){
                 foreach($row as $value){
                     $data[] = $value['menu_id'];
                 }
             }
             
             return $data;
         }
         
         /**
          * 插入角色对应功能
          * 
          * @param $role_id
          * @param $menu_id
          * @param $type
          * 
          * @return array
          * 
          * @version 1.0
          * @date 2013-12-28
          */
         public function _addRoleMenu($role_id, $menu_id, $type = false) {

            $status = 0;

            if ($role_id && $menu_id) {
                $sql = 'INSERT INTO "{{role_priv}}" ("role_id","menu_id") VALUES (:role_id,:menu_id)';
                $result = $this->conn->createCommand($sql);
                $result->bindParam(':role_id', $role_id, PDO::PARAM_STR);
                $result->bindParam(':menu_id', $menu_id, PDO::PARAM_STR);
                $status = $result->execute();
            }

            return $status;
        }
         
        /**
         * 删除角色对应功能
         * 
         * @param $role_id
         * 
         * @return array
         * 
         * @version 1.0
         * @date 2013-12-29
         */
        public function _delRoleMenu($role_id){
            $status = 0;
            
            if($role_id>0){
                $sql = 'DELETE FROM "{{role_priv}}" WHERE "role_id" = :role_id ';
                $result = $this->conn->createCommand($sql);
                $result->bindParam(':role_id', $role_id, PDO::PARAM_STR);
                $status = $result->execute();
            }
            
            return $status;
        }
        
         /**
          * 得到当前学校基本信息
          * 
          * @param $school_id
          * 
          * @return array()
          * 
          * @version 1.0
          * @date 2013-12-15
          * @author jinyue.wang
          */
         public function _getSchool($school_code){
             
             $sql = 'SELECT "school_id","school_name","school_nick","school_code","edu_type_ids" FROM "{{school}}" WHERE "school_code" = :code ';
             $result = $this->conn->createCommand($sql);
             $result->bindParam(':code', $school_code,PDO::PARAM_STR);
             $data = $result->queryRow();
             return $data;
         }
         public function _getSchoolArray($school_id){
             $sql = 'SELECT "school_id","school_name","school_nick","school_code","edu_type_ids" FROM "{{school}}" WHERE "school_id" = :s_sid ';
             $result = $this->conn->createCommand($sql);
             $result->bindParam(':s_sid', $school_id,PDO::PARAM_STR);
             
             $data = $result->queryRow();
             
             return $data;
         }
         
         public function _getSchoolObject(){
             
             $new_data = array();
             
             $sql = 'SELECT "school_id","school_name","school_nick","school_code","edu_type_ids" FROM "{{school}}" ';
             $result = $this->conn->createCommand($sql);
             
             $data = $result->queryAll();
             foreach($data as $value){
                 $new_data[$value['school_id']] = $value['school_name'];
             }
             
             return $new_data;  
         }
         
        /**
         * 得到分数段
         * 
         * @param $start_num
         * @param $edu_num
         * @param $key
         * 
         * @return array
         * 
         * @author jinyue.wang
         * @date 2013-12-15
         * @version 1.0
         */
        public function _getScoreArray($start_num, $end_num, $key) {

            $data = array();
            $q = $end_num - $start_num;

            $page = ceil($q / $key);

            for ($i = 0; $i <= $page; $i++) {
                $new_data = array();
                
                $kNum = $end_num;

                $end_num -= $key;
                if ($end_num < $start_num) {
                    $end_num = $start_num;

                    $keys = $kNum . '-' . $end_num;
                    $new_data['name'] = $keys;
                    $new_data['data'] = array((int)$kNum,(int)$end_num);
                    
                    $kNum = $end_num;
                    break;
                }
                $keys = $kNum . '-' . $end_num;
                $new_data['name'] = $keys;
                $new_data['data'] = array((int)$kNum,(int)$end_num);
                
                $kNum = $end_num;
                
                $data[] = $new_data;
            }
            if(!empty($data)){
                $end_data['name'] = $end_num.'以下';
                $end_data['data'] = array((int)$end_num,0);
                $data[] = $end_data;
            }
            return $data;
    }
    /**
     * 删除导入用户信息
     * 
     * @version 1.0
     * @date 2014-02-06
     */
    public function _delStudent($student_id,$exam_id) {
        $status = 0;
        
        if($student_id>0 && $exam_id>0){
            $sql = 'DELETE FROM "{{student}}" WHERE "student_id" = :s_id AND "exam_id" = :e_id';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':s_id',$student_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
            $status = $result->execute();
        }
        return $status;
    }
    public function _delTeacher($teacher_id,$exam_id){
        $status = 0;
        
        if($teacher_id>0 && $exam_id>0){
            $sql = 'DELETE FROM "{{teacher}}" WHERE "teacher_id" = :t_id AND "exam_id" = :e_id';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':t_id',$teacher_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
            $status = $result->execute();
        }
        return $status;
    }
    public function _delOutScore($student_id,$exam_id){
        $status = 0;
        if($student_id>0 && $exam_id>0){
            $sql = 'DELETE FROM "{{student_out_score}}" WHERE "student_id" = :s_id AND "exam_id" = :e_id';
            $result = $this->conn->createCommand($sql);
            $result->bindParam("s_id",$student_id,PDO::PARAM_STR);
            $result->bindParam("e_id",$exam_id,PDO::PARAM_STR);
            $status = $result->execute();
        }
        return $status;
    }
    public function _delstuScore($student_id,$exam_id){
        $status = 0;
        if($student_id>0 && $exam_id>0){
            $sql = 'DELETE FROM "{{student}}" WHERE "student_id" = :s_id AND "exam_id" = :e_id';
            $result = $this->conn->createCommand($sql);
            $result->bindParam("s_id",$student_id,PDO::PARAM_STR);
            $result->bindParam("e_id",$exam_id,PDO::PARAM_STR);
            $status = $result->execute();
        }
        return $status;
    }
    /**
     * 得到指定学生的基本信息
     * 
     * @version 1.0
     * @date 2014-02-06
     */
    public function _getStudentInfo($student_id,$exam_id){
        $data = array();
        
        if($student_id>0 && $exam_id>0){
            $sql = 'SELECT "student_id","student_name","class_name","exam_id","exam_code","socre","school_id","school_name" FROM "{{student}}" WHERE "student_id" = :s_id AND "exam_id" = :e_id';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':s_id',$student_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
            $data = $result->queryRow();
        }
        return $data;
    }
    public function _getTeacherInfo($teacher_id,$exam_id){
        $data = array();
        
        if($teacher_id>0 && $exam_id>0){
            $sql = 'SELECT "teacher_id","teacher_name","subject_id","subject_name","school_id","school_name","exam_id" FROM "{{teacher}}" WHERE "teacher_id" = :t_id AND "exam_id" = :e_id';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':t_id',$teacher_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
            $data = $result->queryRow();
        }
        return $data;
    }

    public function _getOutExamInfo($student_id,$exam_id){
        $data = array();
        if($student_id>0 && $exam_id>0){
            $sql = 'SELECT "student_id","student_name","exam_id","exam_code","school_name","class_name","socre" FROM "{{student_out_score}}" WHERE "student_id" = :s_id AND "exam_id" = :e_id';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':s_id',$student_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
            $data = $result->queryRow();
        }
        return $data;
    }
    /**
     * 更新学生基本信息
     * 
     * @version 1.0
     * @date 2014-02-06
     */
    public function _updStudentInfo($student_id,$data){
        $status = 0;
        if(!empty($data)){
            $sql = 'UPDATE "{{student}}" SET "student_name" = :s_name,"class_name" = :c_name WHERE "student_id" = :s_id AND "exam_id" = :e_id';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':s_name',$data['student_name'],PDO::PARAM_STR);
            $result->bindParam(':c_name',$data['class_name'],PDO::PARAM_STR);
            $result->bindParam(':s_id',$student_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$data['exam_id'],PDO::PARAM_STR);
            $status = $result->execute();
        }
        return $status;
    }
    public function _updTeacherInfo($teacher_id,$data,$subjectObject){
        $status = 0;
        if(!empty($data)){
            $sub_name = isset($subjectObject[$data['subject_id']]['subject_name'])?$subjectObject[$data['subject_id']]['subject_name']:'';
            $sql = 'UPDATE "{{teacher}}" SET "teacher_name" = :t_name,"subject_id"=:sub_id,"subject_name"=:sub_name WHERE "teacher_id" = :t_id AND "exam_id" = :e_id';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':t_name',$data['teacher_name'],PDO::PARAM_STR);
            $result->bindParam(':sub_id',$data['subject_id'],PDO::PARAM_STR);
            $result->bindParam(':sub_name',$sub_name,PDO::PARAM_STR);
            $result->bindParam(':t_id',$teacher_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$data['exam_id'],PDO::PARAM_STR);
            $status = $result->execute();
        }
        return $status;
    }
    public function _updOutExamScore($student_id,$exam_id,$data){
        $status = 0;
        if(!empty($data)){
            $sql = 'UPDATE "{{student_out_score}}" SET "socre" = :sfen WHERE "student_id" = :s_id AND "exam_id" = :e_id ';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':sfen',$data['socre'],PDO::PARAM_STR);
            $result->bindParam(':s_id',$student_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
            $status = $result->execute();
        }
        return $status;
    }
    public function _updstuExamScore($student_id,$exam_id,$data){
        $status = 0;
        if(!empty($data)){
            $sql = 'UPDATE "{{student}}" SET "socre" = :sfen WHERE "student_id" = :s_id AND "exam_id" = :e_id ';
            $result = $this->conn->createcommand($sql);
            $result->bindParam(':sfen',$data['socre'],PDO::PARAM_STR);
            $result->bindParam(':s_id',$student_id,PDO::PARAM_STR);
            $result->bindParam(':e_id',$exam_id,PDO::PARAM_STR);
            $status = $result->execute();
        }
        return $status;
    }
}
?>